"""
Case Type   : mot不支持DDL
Case Name   : mot不支持Merge into
Create At   : 2020/09/18
Owner       : opentestcase001
Description :
    1.创建模式，创建表，插入数据
    2.Merge into测试，删除模式
    3.恢复环境默认配置，修改enable_incremental_checkpoint为on，并重启数据库
Expect      :
    1.创建模式成功，创建表成功，插入数据成功
    2.Merge into失败，删除模式成功
    3.修改参数成功，重启数据库成功
History     :
    modified by opentestcase042,2023.01.05，优化代码，恢复参数
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class Mot_DDL_test(unittest.TestCase):

    def setUp(self):
        self.logger = Logger()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.commonsh = CommonSH
        self.common = Common()
        self.config = 'enable_incremental_checkpoint'
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.logger.info('------------修改配置，并重启数据库------------')
        show_msg = self.common.show_param(self.config)
        if 'off' not in show_msg:
            msg1 = self.primary_sh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.config}=off')
            self.logger.info(msg1)
            msg2 = self.primary_sh.restart_db_cluster()
            self.logger.info(msg2)
            msg3 = self.primary_sh.get_db_cluster_status()
            self.assertTrue("Normal" or "Degrade" in msg3)
        show_msg = self.common.show_param(self.config)
        self.assertTrue('off' in show_msg)

    def test_mot_Mergeinto(self):
        text = '------------step1:MOT Merge into测试;expect:mot不支持Merge into，失败-------------'
        self.logger.info(text)
        self.schema = 'schema_mot_0053'
        self.t_name = 't_mot_0053'
        self.t_name_1 = 't_mot_0053_1'
        self.sql_cmd = f'''
                        create schema {self.schema};
                        create foreign table {self.schema}.{self.t_name} 
                               (product_id integer, product_name varchar2(60),
                                category varchar2(60));
                        insert into {self.schema}.{self.t_name} values 
                               (1502, 'olympus camera', 'electrncs'),
                               (1601, 'lamaze', 'toys'),
                               (1666, 'harry potter', 'toys'),
                               (1700, 'wait interface', 'books'); 
                        create foreign table {self.schema}.{self.t_name_1} 
                               (product_id integer, product_name varchar2(60), 
                               category varchar2(60)); 
                        insert into {self.schema}.{self.t_name_1} values 
                               (1501, 'vivitar 35mm', 'electrncs'),
                               (1502, 'olympus ', 'electrncs'),
                               (1600, 'play gym', 'toys'),
                               (1601, 'lamaze', 'toys'), 
                               (1666, 'harry potter', 'dvd'); 
                        merge into {self.schema}.{self.t_name_1} np  using 
                               {self.schema}.{self.t_name} p on (np.product_id = p.product_id ) 
                               when matched then update set np.product_name = p.product_name, 
                               np.category = p.category when not matched then  
                               insert values (p.product_id, p.product_name, p.category);  
                        drop schema {self.schema} cascade;        
                      '''
        msg4 = self.primary_sh.execut_db_sql(self.sql_cmd)
        self.logger.info(msg4)
        self.assertIn(self.constant.NOT_SUPPORTED_TYPE, msg4)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, msg4)

    def tearDown(self):
        self.logger.info('------------this is tearDown-------------')
        self.logger.info('-----------恢复配置，enable_incremental_checkpoint=on-----------')
        show_msg = self.common.show_param(self.config)
        if 'on' not in show_msg:
            msg5 = self.primary_sh.execute_gsguc('set',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config}=on')
            self.logger.info(msg5)
            msg6 = self.primary_sh.restart_db_cluster()
            self.logger.info(msg6)
            msg7 = self.primary_sh.get_db_cluster_status()
            self.assertTrue("Normal" or "Degrade" in msg7)
        show_msg = self.common.show_param(self.config)
        self.assertTrue('on' in show_msg)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')


